alter proc rpt_AI_DailyRevenueReport
	@date datetime
as
begin
	declare @firstDate as datetime
	set @firstDate = DATEADD(mm, DATEDIFF(mm, 0, @date), 0)

	declare @dateLY as datetime
	set @dateLY = DATEADD(yy, -1, @date)
	
	declare @firstDateLY as datetime
	set @firstDateLY = DATEADD(mm, DATEDIFF(mm, 0, @dateLY), 0)
	
	declare @lastDate as datetime
	set @lastDate = dateadd(dd,-1,dateadd(mm,datediff(mm,0,@date)+1,0))
	
	declare @numOfDay as int
	set @numOfDay = DATEPART(DD, @lastDate)
	
	declare @curDay as int
	set @curDay = DATEPART(DD, @date)	
		
	declare @tab table(
		PrcCode nvarchar(100),
		PrcName nvarchar(100),
		curAmt numeric(19, 2),
		mtdAmt numeric(19, 2),
		lymtdAmt numeric(19, 2),
		grMask int
	)
	insert into @tab 
	select isnull(pr.PrcCode,'#NA#') PrcCode, isnull(pr.PrcName,'Not specific') PrcName,
	SUM(T.curAmt) curAmt, SUM(T.mtdAmt) mtdAmt, SUM(T.lymtdAmt) lymtdAmt, 4
	from 
	(
		select jd.Account, jd.ProfitCode, 
			jd.Credit-jd.Debit curAmt, 0 mtdAmt, 0 lymtdAmt
			from JDT1 jd		
			where jd.RefDate = @date 		
		union all 
		select jd.Account, jd.ProfitCode, 
			0 curAmt, jd.Credit-jd.Debit mtdAmt, 0 lymtdAmt
			from JDT1 jd			
			where jd.RefDate between @firstDate and @date
		union all 		
		select jd.Account, jd.ProfitCode, 
			0 curAmt, 0 mtdAmt, jd.Credit-jd.Debit lymtdAmt
			from JDT1 jd			
			where jd.RefDate between @firstDateLY and @dateLY

	) T 
	left join OPRC pr on T.ProfitCode = pr.PrcCode
	left join OACT ca on T.Account = ca.AcctCode
	where ca.GroupMask = 4	
	group by  pr.PrcCode, pr.PrcName
	
	select *, 0 curBud from @tab ta
	
	outer apply (select SUM(gt1.DebLTotal - gt1.CredLTotal)/@numOfDay * @curDay  curBud  from 
		OBGS gs 
		left join  OBGT gt on gs.AbsId = gt.Instance
		left join BGT1 gt1 on gt.AbsId = gt1.BudgId and gt1.Line_ID = MONTH(@date) - 1
		left join OACT ct on gt1.AcctCode = ct.AcctCode
		where YEAR(gs.FinancYear) = YEAR(@date) and gs.Locked='N'
		and gs.OcrCode = ta.PrcCode  collate SQL_Latin1_General_CP850_CI_AS
		and ct.GroupMask = ta.grMask) BT

	print @firstDate
	print @dateLY
	print @firstDateLY
end


-- rpt_AI_DailyRevenueReport '07-25-2013'

-- select RefDate, * from JDT1